# -*- coding: utf-8 -*-
#importacao of the modulos

import sqlite3
import csv
import sys

#Joao Alturas_6197
#2013_10_24

#Defenition of the function csvsql 
#paramt csvFile
#paramt bd


def csvsql(csvFile, bd):
	
# connetion sqlite of the base dados
	
	connection = sqlite3.connect(bd)
	
# connection cursor of the bd
	
	c = connection.cursor()
	
#Create table in not exists of the parametre

	c.execute('''create table if not exists dados(codIntituicao varchar(100), codCurso varchar(100),\
				nomeInstituicao varchar(100), nomeCurso varchar(75), grau varchar(22), vagasIniciais varchar(100), \
				colocados varchar(100), notaUltCand varchar(100), vagasSobrantes varchar(100))''')
				
# save the results nd conection	
			
	connection.commit()
	
#Open csvFile and read of the csvFile and delimiter of the column ','

	spamReader = csv.reader(open(csvFile, "r"), delimiter=',')

# Read row of csvFile
	
	for row in spamReader:
		
#Read row of the column 0 of the column 9
# replace de "," par '.' and "\\" '\\\\' (informacao recolhida trabalho 2011/2012)	
	
		for i in range(0,9):
			
			row[i] = row[i].replace(",", r'.');
			
			row[i] = row[i].replace("\\", r'\\\\');
			
#Insert of the dados of the tabela
		
		comando = '''insert or replace into dados (codIntituicao, codCurso,\
				     nomeInstituicao, nomeCurso, grau, vagasIniciais,\
				     colocados, notaUltCand,	vagasSobrantes) values ("{0}","{1}","{2}","{3}","{4}","{5}",\
										                                "{6}","{7}","{8}")'''.format(row[0], row[1], row[2],
										                                 row[3], row[4], row[5], row[6], row[7], row[8])
										                                 
#Caso exista alguma excepcao 
          
		try:
	
# Execute of the variavel "comando"
										                       
			
			c.execute(comando)
			
#Read and save
	
			connection.commit()

#List of the exceptions and list error
			
		except Exception, err:
			print err
			print row
			print
			print 
			
#Close of the connection
	
	connection.close()

	
	pass


csvsql("csvFile.csv", "nomeBd.db")
	
 		

